package com.bosch.in.rtp1.ept.util;

import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JSONSerializer;

import com.bosch.in.rtp1.ept.constant.CommonConstants;
import com.bosch.in.rtp1.ept.constant.FXFactorConstant;
import com.bosch.in.rtp1.ept.constant.FactorType;
import com.bosch.in.rtp1.ept.constant.FilterParameter;
import com.bosch.in.rtp1.ept.constant.FinishRecipeConstant;
import com.bosch.in.rtp1.ept.constant.LotStatusColumnName;
import com.bosch.in.rtp1.ept.constant.QueryKeyWord;
import com.bosch.in.rtp1.ept.constant.ReleasedHoldColumnName;
import com.bosch.in.rtp1.ept.constant.ReleasedHoldParameter;
import com.bosch.in.rtp1.ept.model.technical.Condition4FinishRecipe;
import com.bosch.in.rtp1.ept.model.technical.Condition4FxFactor;
import com.bosch.in.rtp1.ept.model.technical.Condition4HldDurByMonthDiagram;
import com.bosch.in.rtp1.ept.model.technical.Condition4LotStatus;
import com.bosch.in.rtp1.ept.model.technical.Condition4RelHldDiagram;
import com.bosch.in.rtp1.ept.model.technical.Condition4ReleasedHold;

/**
 * Utility supports building sql condition statement based on request parameters of searching criteria
 * @author TPR1HC
 *
 */
public class SQLConditionBuilder {
	
	private static final String RULES = "rules";
	private static final String FIELD = "field";
	private static final String DATA = "data";
	private static final String TOCHAR = " to_char(";
	private static final String DATE_SQL_FORMAT = ", 'DD-MON-YYYY HH24:MI:SS') ";
	
	/**
	 * Extract parameter from request, type casting especially for NUMBER, DATE TIME
	 * and then build dynamic search criteria
	 */
	public static Condition4LotStatus extractLotStatusParameter(HttpServletRequest request) {
    	
		Condition4LotStatus searchCriteria = new Condition4LotStatus();
    	// store parameter values
    	List<Object> sqlParamValues = new ArrayList<Object>();
    	
    	StringBuffer dynamicCondition = new StringBuffer(); 
    	
    	// Build dynamic condition for Department, Priority, HoldCode, Product Area
    	buildCommonCondition4LotStatus(request, searchCriteria, sqlParamValues,dynamicCondition);
    	
		// Build dynamic condition for filter text
		addFilterCondition(request, searchCriteria, sqlParamValues, dynamicCondition);
		
		// Build CustomerName
		String customerName = request.getParameter(FilterParameter.CUSTOMER_NAME);
		customerName = StringUtility.convertToEmptyIfNull(customerName);
		if (customerName.isEmpty()) {
			sqlParamValues.add(QueryKeyWord.WILDCARD);
		} else {
			customerName = customerName.toUpperCase();
			sqlParamValues.add(QueryKeyWord.WILDCARD + customerName + QueryKeyWord.WILDCARD);
		}
		searchCriteria.setCustomerName(customerName);
		
		// Build Hold Reason 
		String holdReason = StringUtility.convertToEmptyIfNull(request.getParameter(FilterParameter.HOLD_REASON));
		if (holdReason.isEmpty()) {
			sqlParamValues.add(QueryKeyWord.WILDCARD);
		} else {
			sqlParamValues.add(QueryKeyWord.WILDCARD + holdReason + QueryKeyWord.WILDCARD);
		}
		searchCriteria.setHoldReason(holdReason);
    	
		// Build ORDER BY clause
		searchCriteria.setOrderBy(getOrderBy(request, true));
		
    	String dynamicSql = SQLStatementLoader.getLotStatusQuery();
    	// Insert dynamic condition
    	dynamicSql = dynamicSql.replace(CommonConstants.DYNAMIC_CONDITION, dynamicCondition.toString());
    	// Insert order by clause
    	dynamicSql = dynamicSql.replace(CommonConstants.ORDER_BY, searchCriteria.getOrderBy());
    	
    	searchCriteria.setSqlQuery(dynamicSql);
    	searchCriteria.setParameterValues(sqlParamValues);
    	return searchCriteria;
	}

	/**
	 * Build orderby clause from request parameters.
	 * @param request
	 * @return
	 */
	private static String getOrderBy(HttpServletRequest request, boolean isLot) {
		StringBuffer orderBy = new StringBuffer();
		String field = request.getParameter(FilterParameter.ORDER_BY);
		
		if (isLot) {
			// Always have default order by grouping priority and waittime
			orderBy.append(QueryKeyWord.BLANK + QueryKeyWord.ORDER + QueryKeyWord.BLANK);
			
			if (LotStatusColumnName.PRIORITY_DB.equals(field)) {
				orderBy.append(field + QueryKeyWord.BLANK 
						+ request.getParameter(FilterParameter.ORDER) + QueryKeyWord.COMMA
						+ LotStatusColumnName.WAIT_TIME_DB + QueryKeyWord.BLANK + QueryKeyWord.DESC);
			} else if (LotStatusColumnName.WAIT_TIME_DB.equals(field)) {
				orderBy.append(LotStatusColumnName.PRIORITY_DB + QueryKeyWord.COMMA 
						+ LotStatusColumnName.WAIT_TIME_DB + QueryKeyWord.BLANK + request.getParameter(FilterParameter.ORDER));
			} else {
				if (field != null && !field.equals("")) {
					orderBy.append(field + QueryKeyWord.BLANK 
							+ request.getParameter(FilterParameter.ORDER) + QueryKeyWord.COMMA);
				}
				orderBy.append(LotStatusColumnName.PRIORITY_DB + QueryKeyWord.COMMA 
						+ LotStatusColumnName.WAIT_TIME_DB + QueryKeyWord.BLANK + QueryKeyWord.DESC);
			}
			
		} else {
			if (field != null && !field.equals("")) {
				orderBy.append(QueryKeyWord.BLANK + QueryKeyWord.ORDER + QueryKeyWord.BLANK 
						+ field + QueryKeyWord.BLANK 
						+ request.getParameter(FilterParameter.ORDER));
			}
		}
		return orderBy.toString();
	}
	
	/**
	 * add filter condition on individual column if having
	 * @param request
	 * @param searchCriteria
	 * @param sqlParamValues
	 * @param dynamicCondition
	 */
	private static void addFilterCondition(HttpServletRequest request,
			Condition4LotStatus searchCriteria, List<Object> sqlParamValues,
			StringBuffer dynamicCondition) {
		String filterStr = request.getParameter(FilterParameter.FILTERS);
		if (filterStr != null && !filterStr.equals("")) {
			JSONObject jsonObj = (JSONObject) JSONSerializer.toJSON(filterStr);
			JSONArray jsonFilters = jsonObj.getJSONArray(RULES);
			for(int i = 0; i < jsonFilters.size(); i++) {
				JSONObject filter = (JSONObject) jsonFilters.get(i);
				String field = (String) filter.get(FIELD);
				String data = (String) filter.get(DATA);
				
				// Handle calculated fields or Number field which is rounded up, don't add sql parameter
				if (field.equals(LotStatusColumnName.WAIT_TIME_DB)) {
					searchCriteria.setWaittime(data);
					continue;
				} else if (field.equals(ReleasedHoldColumnName.HELD_DURATION_DB)) {
					if (searchCriteria instanceof Condition4ReleasedHold) {
						((Condition4ReleasedHold)searchCriteria).setHeldDuration(data);
					}
					continue;
				} else if(field.equals(LotStatusColumnName.X_PLAN_DB)) {
					searchCriteria.setX_plan(data);
					continue;
				} else if(field.equals(LotStatusColumnName.X_RWH_DB)) {
					searchCriteria.setX_rwh(data);
					continue;
				} else if(field.equals(LotStatusColumnName.X_RW_DB)) {
					searchCriteria.setX_rw(data);
					continue;
				}
				
				// Value will be translated to UPPER CASE for almost fields except of LOTCMNT and RECIPE TITLE fields.
				if (!field.equals(LotStatusColumnName.LOT_COMMENT_DB) && !field.equals(LotStatusColumnName.RECIPE_TITLE_DB)) {
					data = data.toUpperCase();
				}
				
				// condition for datetime: to_char(field, 'DD-MON-YY HH24:MI') like '%date%' 
				if (field.equals(LotStatusColumnName.START_TIME_DB) || field.equals(LotStatusColumnName.QUEUE_TIME_DB)
						|| field.equals(ReleasedHoldColumnName.HALT_START_DB) || field.equals(ReleasedHoldColumnName.HALT_END_DB)) {
					dynamicCondition.append(TOCHAR + field + DATE_SQL_FORMAT + QueryKeyWord.BLANK + QueryKeyWord.LIKE 
							+ QueryKeyWord.BLANK + QueryKeyWord.PLACEHOLDER + QueryKeyWord.BLANK + QueryKeyWord.AND);
					sqlParamValues.add(QueryKeyWord.WILDCARD + data + QueryKeyWord.WILDCARD);
				} else {
					// condition: field LIKE '%value%'
					dynamicCondition.append(QueryKeyWord.BLANK + field + QueryKeyWord.BLANK + QueryKeyWord.LIKE + 
							QueryKeyWord.BLANK + QueryKeyWord.PLACEHOLDER + QueryKeyWord.BLANK + QueryKeyWord.AND);
					sqlParamValues.add(QueryKeyWord.WILDCARD + data + QueryKeyWord.WILDCARD);
				}
			}
		}
	}

	/**
	 * Add condition to check ProdArea.
	 * @param request
	 * @param searchCriteria
	 * @param sqlParamValues
	 * @param dynamicCondition
	 */
	private static void addProdAreaCondition(HttpServletRequest request,
			Condition4LotStatus searchCriteria, List<Object> sqlParamValues,
			StringBuffer dynamicCondition, String columnName) {
		String[] prodAreas = request.getParameterValues(FilterParameter.PROD_AREA);
		if (prodAreas != null && prodAreas.length > 0) {
			searchCriteria.setProductArea(StringUtility.arrayToString(prodAreas));
		
			buildOrCondition(dynamicCondition, columnName, true, prodAreas, sqlParamValues);
		}
	}

	/**
	 * Add condition to check HoldCode
	 * @param request
	 * @param searchCriteria
	 * @param sqlParamValues
	 * @param dynamicCondition
	 */
	private static void addHoldCodeCondition(HttpServletRequest request,
			Condition4LotStatus searchCriteria, List<Object> sqlParamValues,
			StringBuffer dynamicCondition, String columnName, String parameterName) {
		// get both HoldText + '+=+' + HoldCodes
		String holdText_Codes = request.getParameter(parameterName);
			
		// extract holdcodes
		String holdCodes = HoldCodeUtility.extractHoldCodes(holdText_Codes);
		if (holdCodes != null && !holdCodes.equals("")) {
		
			// replace any character '*' by '%'
			String patternsStr = holdCodes.replaceAll(QueryKeyWord.STAR, QueryKeyWord.WILDCARD);
			// split parameter into holdcode filter values
			List<String> holdCodeFilters = StringUtility.extractHCPatterns(patternsStr);
			String []strArray = new String[holdCodeFilters.size()];
			holdCodeFilters.toArray(strArray);
			buildOrCondition(dynamicCondition, columnName, false, strArray, sqlParamValues);
			
			// store parameter value in nice format
			holdCodes = StringUtility.convertHCPatternStr(holdCodes);
		}
		
		if (columnName.equals(ReleasedHoldColumnName.HOLD_CODE_RECLASSIFIED_DB)) {
			// extract hold reclassified text
			String holdText = HoldCodeUtility.extractHoldText(holdText_Codes);
			if (searchCriteria instanceof Condition4ReleasedHold) {
				((Condition4ReleasedHold)searchCriteria).setHoldCodeReclassifiedText(holdText);			
				((Condition4ReleasedHold)searchCriteria).setHoldCodeReclassified(holdCodes);
			}
			
		} else {
			searchCriteria.setHoldCode(holdCodes);
			// extract hold text
			searchCriteria.setHoldCodeText(HoldCodeUtility.extractHoldText(holdText_Codes));
		}
	}


	/**
	 * Add condition to check Priority
	 * @param request
	 * @param searchCriteria
	 * @param sqlParamValues
	 * @param dynamicCondition
	 */
	private static void addPriorityCondition(HttpServletRequest request,
			Condition4LotStatus searchCriteria, List<Object> sqlParamValues,
			StringBuffer dynamicCondition, String columnName) {
		String[] priorityValues = request.getParameterValues(FilterParameter.PRIORITY);
		if (priorityValues != null && priorityValues.length > 0) {
			searchCriteria.setPriority(StringUtility.arrayToString(priorityValues));
		
			buildOrCondition(dynamicCondition, columnName, true, priorityValues, sqlParamValues);
		}
	}


	/**
	 * Add condition to check Department
	 * @param request
	 * @param searchCriteria
	 * @param sqlParamValues
	 * @param dynamicCondition
	 */
	private static void addDepartmentCondition(HttpServletRequest request,
			Condition4LotStatus searchCriteria, List<Object> sqlParamValues,
			StringBuffer dynamicCondition, String columnName) {
		String[] departmentValues = request.getParameterValues(FilterParameter.DEPARTMENT);
    	if (departmentValues != null && departmentValues.length > 0) {
	    	searchCriteria.setDepartment(StringUtility.arrayToString(departmentValues));
			
			// pattern for searching department: '%name%'
			for (int i = 0; i < departmentValues.length; i++) {
				departmentValues[i] = QueryKeyWord.WILDCARD + departmentValues[i].toUpperCase() + QueryKeyWord.WILDCARD;
			}
			buildOrCondition(dynamicCondition, columnName, false, departmentValues, sqlParamValues);
    	}
	}
	
	
	/**
	 * Build condition if search attribute can be matched to either one of given list of values.
	 * @param searchCondition	string builder
	 * @param columnName	column name
	 * @param equalOperator	flag indicates if value is compared by equal or like operator
	 * @param parameterValues	list of possible values
	 * @param sqlParamValues	cumulative list of parameter value
	 */
	private static void buildOrCondition(StringBuffer searchCondition, String columnName, boolean equalOperator,
			String[] parameterValues, List<Object> sqlParamValues) {
		// Open bracket
		// If column name is P3H_HOLD_CODE_RECLASSIFIED and parameter of REST
		if (columnName.equals(ReleasedHoldColumnName.HOLD_CODE_RECLASSIFIED_DB) &&
				HoldCodeUtility.isRest(parameterValues)) {
			searchCondition.append(QueryKeyWord.BLANK + QueryKeyWord.NOT + QueryKeyWord.BLANK + QueryKeyWord.OPEN);
		} else {
			searchCondition.append(QueryKeyWord.BLANK + QueryKeyWord.OPEN);
		}
		for (int i = 0; i < parameterValues.length; i++) {
			if (equalOperator) { // if parameter is integer, using EQUAL condition
				searchCondition.append(QueryKeyWord.BLANK + columnName
					+ QueryKeyWord.BLANK + QueryKeyWord.EQUAL + QueryKeyWord.BLANK
					+ QueryKeyWord.PLACEHOLDER + QueryKeyWord.BLANK);
				
			} else {	// if parameter is string, using LIKE condition
				searchCondition.append(QueryKeyWord.BLANK + columnName
						+ QueryKeyWord.BLANK + QueryKeyWord.LIKE + QueryKeyWord.BLANK
						+ QueryKeyWord.PLACEHOLDER + QueryKeyWord.BLANK);
				
			}
			// add parameter value accordingly
			sqlParamValues.add(parameterValues[i]);
			
			// Still have parameter value --> add OR to the condition clause
			if (i < parameterValues.length - 1) {
				searchCondition.append(QueryKeyWord.OR);
			}
		}
		// Close bracket
		searchCondition.append(QueryKeyWord.CLOSE + QueryKeyWord.BLANK + QueryKeyWord.AND);
	}
	
	/**
	 * Extract parameter from request, type casting especially for YEAR, PRODAREA and PRIORITY
	 * and then build condition for FX Factor
	 */
	
	@SuppressWarnings("unchecked")
	public static Condition4FxFactor extractCondition4FXFactor(HttpServletRequest request)
	{
		Condition4FxFactor condition4FxFactor = new Condition4FxFactor();
		Iterator<Map.Entry<String, String[]>> entries = request.getParameterMap().entrySet().iterator();
    	while(entries.hasNext()) {
    		Map.Entry<String, String[]> entry = entries.next();    
    		String parameterName = entry.getKey();
    		String[] parameterValues = entry.getValue();
    		if (parameterName.equalsIgnoreCase(FXFactorConstant.YEAR))
    		{
    			condition4FxFactor.setYear(Integer.parseInt(parameterValues[0]));
    		}
    		else if (parameterName.equalsIgnoreCase(FXFactorConstant.PRODAREA))
    		{
    			condition4FxFactor.setProdArea(Integer.parseInt(parameterValues[0]));
    		}
    		else if (parameterName.equalsIgnoreCase(FXFactorConstant.PRIORITY))
    		{
    			condition4FxFactor.setPriority(Integer.parseInt(parameterValues[0]));
    		}
    	}
    	// add conditions to parameters list
    	condition4FxFactor.builParameterValuesList();
    	
    	return condition4FxFactor;
	}	
	
	/**
	 * Extract parameter from request, type casting especially for YEAR, PRODAREA and PRIORITY
	 * and then build condition for FX Factor
	 */
	
	@SuppressWarnings("unchecked")
	public static Condition4FinishRecipe extractCondition4FinishRecipe(HttpServletRequest request)
	{
		Condition4FinishRecipe condition4FinishRecipe = new Condition4FinishRecipe();
		
		Iterator<Map.Entry<String, String[]>> entries = request.getParameterMap().entrySet().iterator();
    	while(entries.hasNext()) {
    		Map.Entry<String, String[]> entry = entries.next();    
    		String parameterName = entry.getKey();
    		String[] parameterValues = entry.getValue();
    		String parameterValue = "";
    		if (parameterValues != null && parameterValues.length>0)
    		{
    			parameterValue = StringUtility.convertToEmptyIfNull(parameterValues[0]);
    		}
    			
    		if (parameterName.equalsIgnoreCase(FinishRecipeConstant.EQPTYPE_PARAM))
    		{
    			if (parameterValue.equalsIgnoreCase(FinishRecipeConstant.EMPTY_EQP_TYPE_STRING))
    			{
    				condition4FinishRecipe.setEqpType(FinishRecipeConstant.EMPTY_EQP_TYPE_VALUE);
    			}
    			else
    			{
    				condition4FinishRecipe.setEqpType(parameterValue);
    			}
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.EQUIPID_PARAM))
    		{
    			condition4FinishRecipe.setEquipID(parameterValue);
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.LOCATION_PARAM))
    		{
    			condition4FinishRecipe.setLocation(parameterValue);
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.PART_ID_PARAM))
    		{
    			condition4FinishRecipe.setPartId(parameterValue);
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.STAGE_PARAM))
    		{
    			condition4FinishRecipe.setStage(parameterValue);
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.PRIORITY_PARAM))
    		{
    			condition4FinishRecipe.setPriority(Integer.parseInt(parameterValue));
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.PRODAREA_PARAM))
    		{
    			condition4FinishRecipe.setProdArea(Integer.parseInt(parameterValue));
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.YEAR_PARAM))
    		{
    			condition4FinishRecipe.setYear(Integer.parseInt(parameterValue));
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.CUSTOMER_PARAM))
    		{
    			condition4FinishRecipe.setCustomerName(parameterValue);
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.FACTOR_TYPE_PARAM))
    		{
    			int factorType = -1;
    			if (parameterValue.equalsIgnoreCase(FactorType.X_PLAN.toString()))
    			{
    				factorType = FinishRecipeConstant.X_PLAN;
    			}
    			else if (parameterValue.equalsIgnoreCase(FactorType.X_RW.toString()))
    			{
    				factorType = FinishRecipeConstant.X_RW;
    			}
    			else if (parameterValue.equalsIgnoreCase(FactorType.X_RWH.toString()))
    			{
    				factorType = FinishRecipeConstant.X_RWH;
    			}
    			condition4FinishRecipe.setFactorType(factorType);
    		}
    		else if (parameterName.equalsIgnoreCase(FinishRecipeConstant.MONTH_PARAM))
    		{
    			condition4FinishRecipe.setMonth(getMonth(parameterValue));
    		}
    	}
    	// add conditions to parameters list
		condition4FinishRecipe.builParameterValuesList();
		
		return condition4FinishRecipe;
	}	
	
	private static int getMonth(String month) {
		month = month.trim();
		for (int i=0; i< FinishRecipeConstant.NUM_OF_TIME_POINT; ++i)
		{
			if (CommonConstants.MONTH_LIST[i].equalsIgnoreCase(month))
			{
				return i;
			}
		}
		return 0;
	}

	/**
	 * Extract parameter from request,
	 * and then build query statement for ReleasedHold
	 * @throws ParseException 
	 */
	
	public static Condition4ReleasedHold extractReleasedHoldParameters(HttpServletRequest request) throws ParseException
	{
		Condition4ReleasedHold searchCriteria = new Condition4ReleasedHold();
		
    	// store parameter values
    	List<Object> sqlParamValues = new ArrayList<Object>();
    	
    	StringBuffer dynamicCondition = new StringBuffer(); 
    	
    	// Build dynamic condition for Department, Priority, HoldCode, Product Area, HoldCodeReclassified, HoldEnd from and to
    	buildCommonCondition4ReleasedHold(request, searchCriteria, sqlParamValues,dynamicCondition);
    	
		// Build dynamic condition for filter text
		addFilterCondition(request, searchCriteria, sqlParamValues, dynamicCondition);
		
		// Customer name
		String customerName = request.getParameter(FilterParameter.CUSTOMER_NAME);
		customerName = StringUtility.convertToEmptyIfNull(customerName);
		if (customerName.isEmpty()) {
			sqlParamValues.add(QueryKeyWord.WILDCARD);
		} else {
			customerName = customerName.toUpperCase();
			sqlParamValues.add(QueryKeyWord.WILDCARD + customerName + QueryKeyWord.WILDCARD);
		}
		searchCriteria.setCustomerName(customerName);
		// Hold reason
		String holdReason = StringUtility.convertToEmptyIfNull(request.getParameter(FilterParameter.HOLD_REASON));
		if (holdReason.isEmpty()) {
			sqlParamValues.add(QueryKeyWord.WILDCARD);
		} else {
			sqlParamValues.add(QueryKeyWord.WILDCARD + holdReason + QueryKeyWord.WILDCARD);
		}
		searchCriteria.setHoldReason(holdReason);
		// Hold comment reclassified
		String hCommentRecls = StringUtility.convertToEmptyIfNull(request.getParameter(FilterParameter.HOLD_COMMENT_RECLASSIFIED));
		if (hCommentRecls.isEmpty()) {
			sqlParamValues.add(QueryKeyWord.WILDCARD);
		} else {
			sqlParamValues.add(QueryKeyWord.WILDCARD + hCommentRecls + QueryKeyWord.WILDCARD);
		}
		searchCriteria.setHoldCommentReclassified(hCommentRecls);
    	
		// Build ORDER BY clause
		searchCriteria.setOrderBy(getOrderBy(request, false));
		
    	String dynamicSql = SQLStatementLoader.getReleasedHoldQuery();
    	// Insert dynamic condition
    	dynamicSql = dynamicSql.replace(CommonConstants.DYNAMIC_CONDITION, dynamicCondition.toString());
    	// Insert order by clause
    	dynamicSql = dynamicSql.replace(CommonConstants.ORDER_BY, searchCriteria.getOrderBy());
    	
    	searchCriteria.setSqlQuery(dynamicSql);
    	searchCriteria.setParameterValues(sqlParamValues);
		return searchCriteria;
	}

	/**
	 * Build condition for HoldEnd from and to
	 * @param request
	 * @param sqlParamValues
	 * @param dynamicCondition
	 * @throws ParseException 
	 */
	private static void addCondition4HoldEnd(HttpServletRequest request, Condition4ReleasedHold searchCriteria,
			List<Object> sqlParamValues, StringBuffer dynamicCondition) throws ParseException {
		String holdEndFrom = request.getParameter(ReleasedHoldParameter.HOLD_END_FROM);
		String holdEndTo = request.getParameter(ReleasedHoldParameter.HOLD_END_TO);
		
		SimpleDateFormat df = new SimpleDateFormat(CommonConstants.SHORT_DATE_FORMAT);
		Calendar calendar = Calendar.getInstance();
		
    	if (!holdEndFrom.equals("") && !holdEndTo.equals("")) {
    		// build condition: holdEnd >= from and holdEnd <= to
    		dynamicCondition.append(QueryKeyWord.OPEN + QueryKeyWord.BLANK + ReleasedHoldColumnName.HALT_END_DB  + QueryKeyWord.BLANK
    				+ QueryKeyWord.GREATER + QueryKeyWord.BLANK + QueryKeyWord.PLACEHOLDER + QueryKeyWord.BLANK  + QueryKeyWord.AND + QueryKeyWord.BLANK
    				+ ReleasedHoldColumnName.HALT_END_DB + QueryKeyWord.BLANK + QueryKeyWord.LESS + QueryKeyWord.BLANK + QueryKeyWord.PLACEHOLDER
    				+ QueryKeyWord.CLOSE + QueryKeyWord.BLANK + QueryKeyWord.AND);
    		
    		// add parameter value
    		Date holdEndFromDate = new Date(df.parse(holdEndFrom).getTime());
    		searchCriteria.setHoldEndFrom(holdEndFromDate);
    		sqlParamValues.add(holdEndFromDate);
    		// We get data up to END time of TO date
    		calendar.setTime(df.parse(holdEndTo));
    		searchCriteria.setHoldEndTo(calendar.getTime());
    		
    		// sql parameter value 
    		calendar.add(Calendar.DATE, 1);
    		Date holdEndToDate = new Date(calendar.getTime().getTime());
    		sqlParamValues.add(holdEndToDate);
    	} else if (!holdEndFrom.equals("")) {
    		// build condition: holdEnd >= from
    		dynamicCondition.append(QueryKeyWord.BLANK + ReleasedHoldColumnName.HALT_END_DB  + QueryKeyWord.BLANK
    				+ QueryKeyWord.GREATER + QueryKeyWord.BLANK + QueryKeyWord.PLACEHOLDER + QueryKeyWord.BLANK + QueryKeyWord.AND);
    		
    		searchCriteria.setHoldEndFrom(new Date(df.parse(holdEndFrom).getTime()));
    		sqlParamValues.add(new Date(df.parse(holdEndFrom).getTime()));
    		
    	} else if (!holdEndTo.equals("")) {
    		// build condition: holdEnd <= to
    		dynamicCondition.append(QueryKeyWord.BLANK + ReleasedHoldColumnName.HALT_END_DB + QueryKeyWord.BLANK + QueryKeyWord.LESS 
    				+ QueryKeyWord.BLANK + QueryKeyWord.PLACEHOLDER + QueryKeyWord.BLANK + QueryKeyWord.AND);
    		// We get data up to END time of TO date
    		calendar.setTime(df.parse(holdEndTo));
    		searchCriteria.setHoldEndTo(calendar.getTime());
    		
    		// sql parameter value 
    		calendar.add(Calendar.DATE, 1);
    		sqlParamValues.add(new Date(calendar.getTime().getTime()));
    		
    	}
	}

	/**
	 * Build common condition for Lot status
	 * @param request
	 * @param searchCriteria
	 * @param sqlParamValues
	 * @param dynamicCondition
	 */
	private static void buildCommonCondition4LotStatus(HttpServletRequest request,
			Condition4LotStatus searchCriteria, List<Object> sqlParamValues,
			StringBuffer dynamicCondition) {
		// Build dynamic condition for Department
    	addDepartmentCondition(request, searchCriteria, sqlParamValues, dynamicCondition, LotStatusColumnName.CUSTOMER_NAME_DB);
    	
		// Build dynamic condition for Priority
		addPriorityCondition(request, searchCriteria, sqlParamValues, dynamicCondition, LotStatusColumnName.PRIORITY_DB);
		
		// Build dynamic condition for HoldCode
		addHoldCodeCondition(request, searchCriteria, sqlParamValues, dynamicCondition, LotStatusColumnName.HOLD_CODE_DB, FilterParameter.HOLD_CODE);
    	
		// Build dynamic condition for Product Area
		addProdAreaCondition(request, searchCriteria, sqlParamValues, dynamicCondition, LotStatusColumnName.PRO_AREA_DB);
	}
	
	/**
	 * Build common condition for Released Hold
	 * @param request
	 * @param searchCriteria
	 * @param sqlParamValues
	 * @param dynamicCondition
	 * @throws ParseException 
	 */
	private static void buildCommonCondition4ReleasedHold(HttpServletRequest request,
			Condition4ReleasedHold searchCriteria, List<Object> sqlParamValues,
			StringBuffer dynamicCondition) throws ParseException {
		// Build dynamic condition for Department
    	addDepartmentCondition(request, searchCriteria, sqlParamValues, dynamicCondition, ReleasedHoldColumnName.CUSTOMER_NAME_DB);
    	
		// Build dynamic condition for Priority
		addPriorityCondition(request, searchCriteria, sqlParamValues, dynamicCondition, ReleasedHoldColumnName.PRIORITY_DB);
		
		// Build dynamic condition for HoldCode
		addHoldCodeCondition(request, searchCriteria, sqlParamValues, dynamicCondition, ReleasedHoldColumnName.HOLD_CODE_DB, FilterParameter.HOLD_CODE);
    	
		// Build dynamic condition for HoldCode reclassified
		addHoldCodeCondition(request, searchCriteria, sqlParamValues, dynamicCondition, ReleasedHoldColumnName.HOLD_CODE_RECLASSIFIED_DB, FilterParameter.HOLD_CODE_RECLASSIFIED);
		
		// Build dynamic condition for Product Area
		addProdAreaCondition(request, searchCriteria, sqlParamValues, dynamicCondition, ReleasedHoldColumnName.PRO_AREA_DB);
		
		// Build dynamic condition for HoldEnd from and to
		addCondition4HoldEnd(request, searchCriteria, sqlParamValues, dynamicCondition);
	}
	
	/**
	 * Extract parameter from request, type casting especially for YEAR, Diagram Type
	 * and then build condition for FX Factor
	 */
	@SuppressWarnings("unchecked")
	public static Condition4RelHldDiagram extrCondition4RelHldDiagram(HttpServletRequest request)
	{
		Condition4RelHldDiagram condition4RelHldDiagram = new Condition4RelHldDiagram();
		Iterator<Map.Entry<String, String[]>> entries = request.getParameterMap().entrySet().iterator();
    	while(entries.hasNext()) {
    		Map.Entry<String, String[]> entry = entries.next();    
    		String parameterName = entry.getKey();
    		String[] parameterValues = entry.getValue();
    		if (parameterName.equalsIgnoreCase(ReleasedHoldParameter.DIAGRAM_YEAR_PARAMETER))
    		{
    			condition4RelHldDiagram.setYear(Integer.parseInt(parameterValues[0]));
    		}
    		else if (parameterName.equalsIgnoreCase(ReleasedHoldParameter.DIAGRAM_TYPE_PARAMETER))
    		{
    			condition4RelHldDiagram.setChartType(Integer.parseInt(parameterValues[0]));
    		}
    	}
    	// add conditions to parameters list
    	condition4RelHldDiagram.builParameterValuesList();
    	
    	return condition4RelHldDiagram;
	}
	
	/**
	 * Extract parameter from request to Condition4HldDurByMonthDiagram
	 */
	@SuppressWarnings("unchecked")
	public static Condition4HldDurByMonthDiagram extrCondition4HldDurByMonthDiagram(HttpServletRequest request)
	{
		Condition4HldDurByMonthDiagram cDurByMonthDiagram = new Condition4HldDurByMonthDiagram();
		Iterator<Map.Entry<String, String[]>> entries = request.getParameterMap().entrySet().iterator();
    	while(entries.hasNext()) {
    		Map.Entry<String, String[]> entry = entries.next();    
    		String parameterName = entry.getKey();
    		String[] parameterValues = entry.getValue();
    		if (parameterName.equalsIgnoreCase(ReleasedHoldParameter.DIAGRAM_YEAR_PARAMETER))
    		{
    			cDurByMonthDiagram.setYear(Integer.parseInt(parameterValues[0]));
    		}
    		else if (parameterName.equalsIgnoreCase(ReleasedHoldParameter.DIAGRAM_MONTH_PARAMETER))
    		{
    			cDurByMonthDiagram.setMonth(Integer.parseInt(parameterValues[0]));
    		}
    		else if (parameterName.equalsIgnoreCase(ReleasedHoldParameter.DIAGRAM_PRIO_PARAMETER))
    		{
    			cDurByMonthDiagram.setPriority(Integer.parseInt(parameterValues[0]));
    		}
    	}
    	// add conditions to parameters list
    	cDurByMonthDiagram.builParameterValuesList();
    	
    	return cDurByMonthDiagram;
	}
	
}
